package DBLayer;

import Model.Customer;
import Model.Employee;
import Model.Invoice;
import Model.Product;
import Model.SaleOrder;
import Utils.TimeFormat;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

/**
 *
 * @author Administrator
 */
public class DbSaleOrder implements DbInterfaceSaleOrder {
	private Connection db_con;

	public DbSaleOrder() {
		db_con = DbConnection.getInstance().getDBcon();
	}

	public ArrayList<SaleOrder> getAllSaleOrders(String wClause, boolean retrieveAssociation) {
		return miscWhere(wClause, retrieveAssociation);
	}

	public SaleOrder getById(String id, boolean retrieveAssociation) {
		String wClause = "id = " + id;
        return singleWhere(wClause, retrieveAssociation);
	}

	public SaleOrder getByCustomerId(String id, boolean retrieveAssociation) {
		String wClause = "customer_id = " + id;
		return singleWhere(wClause, retrieveAssociation);
	}

	public SaleOrder getByEmployeeId(String id, boolean retrieveAssociation) {
		String wClause = "employee_id = " + id;
		return singleWhere(wClause, retrieveAssociation);
	}

	public SaleOrder getByInvoiceId(String id, boolean retrieveAssociation) {
		String wClause = "invoice_no = " + id;
		return singleWhere(wClause, retrieveAssociation);
	}

	public int insertSaleOrder(SaleOrder order) {
		int result = -1;
        String query = "INSERT INTO orders(delivery_date, delivery_status, customer_id, employee_id, invoice_no) VALUES"
                + "('" + order.getDeliveryDate() + "', '" + order.getDeliveryStatus() + "', " + order.getCustomer().getId()
				+ ", " + order.getEmployee().getId() + ", " + order.getInvoice().getId() + ")";
        System.out.println("insert: " + query);
        try {
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex) {
            System.out.println("Insert exception in orders db: " + ex);
        }
		insertProducts(order.getProducts(), order.getInvoice().getId());
        return result;
	}

	private int insertProducts(HashMap<Product, Integer> list, int invoice_no) {
		int result = -1;
		int order_id = getByInvoiceId(invoice_no+"", false).getId();
		Iterator<Product> it = list.keySet().iterator();
		while (it.hasNext()) {
			Product prod = it.next();
			String query = "INSERT INTO order_has(order_id, product_id, amount) VALUES"
					+ "('" + order_id + "', '" + prod.getId() + "', '" + list.get(prod) + ")";
			System.out.println("insert: " + query);
			try {
				Statement stmt = db_con.createStatement();
				stmt.setQueryTimeout(5);
				result = stmt.executeUpdate(query);
				stmt.close();
			} catch (Exception ex) {
				System.out.println("Insert exception in order_has db: " + ex);
			}
		}
        return result;
	}

	public int updateSaleOrder(SaleOrder order) {
		int result = -1;

		String query="UPDATE orders SET ";
		if (order.getDeliveryDate() != null) query += "delivery_date = '"+order.getDeliveryDate()+"',";
		if (order.getDeliveryStatus() != null) query += "delivery_status = '"+order.getDeliveryStatus()+"',";
		if (order.getCustomer() != null) query += "customer_id = "+order.getCustomer().getId()+",";
		if (order.getEmployee() != null) query += "employee_id = "+order.getEmployee().getId()+",";
		if (order.getInvoice() != null) query += "invoice_id = "+order.getInvoice().getId()+",";
		if (query.endsWith(",")) query = query.substring(0, query.length()-1);
		query += " WHERE id = "+ order.getId();
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);

	 	 	stmt.close();
		} catch(Exception ex) {
	 	 	System.out.println("Update exception in order db: "+ex);
	  	}
		return result;
	}

	public int deleteSaleOrder(String saleNumber) {
		throw new UnsupportedOperationException("Not supported yet.");
	}


		private ArrayList<SaleOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<SaleOrder> list = new ArrayList<SaleOrder>();

	    String query =  buildQuery(wClause);

        try{ // read the employee from the database
			Statement stmt = db_con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			int snr=0;
			while( results.next() ){
				SaleOrder orderObj = new SaleOrder();
				orderObj = buildSaleOrder(results);
		        list.add(orderObj);
			}//end while
			if (retrieveAssociation) {
				for (SaleOrder order : list)
					retrieveAssociation(order);
			}
			stmt.close();
		} catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return (list.size() > 0 ? list : null);
	}

	//Singelwhere is used when we only select one employee
	private SaleOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SaleOrder orderObj = null;
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
				orderObj = buildSaleOrder(results);
				stmt.close();
				if (retrieveAssociation)
					retrieveAssociation(orderObj);
			}
		} catch(Exception e) {
	 		System.out.println("Query exception: " + e);
	 	}
		return orderObj;
	}

	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM orders";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

		//method to build an employee object
	private SaleOrder buildSaleOrder(ResultSet results)
	{
		SaleOrder orderObj = new SaleOrder();
        try {
			orderObj.setId(Integer.parseInt(results.getString(1)));
			orderObj.setDeliveryDate(TimeFormat.string_to_gregorian(results.getString(2)));
			orderObj.setDeliveryStatus(results.getString(3));
			orderObj.setCustomer(new Customer(Integer.parseInt(results.getString(4))));
			orderObj.setEmployee(new Employee(Integer.parseInt(results.getString(5))));
			orderObj.setInvoice(new Invoice(Integer.parseInt(results.getString(6))));
		} catch(Exception e) {
			System.out.println("error in building the order object");
        }
		return orderObj;
	}

	private void retrieveAssociation(SaleOrder order) {
		int invoice_id = order.getInvoice().getId();
		Invoice invoiceObj = new DbInvoice().searchByNo(invoice_id+"");
		order.setInvoice(invoiceObj);
		System.out.println("Invoice is seleceted");

		int employee_id = order.getEmployee().getId();
		Employee empObj = new DbEmployee().searchById(employee_id+"");
		order.setEmployee(empObj);
		System.out.println("Employee is seleceted");

		int customer_id = order.getCustomer().getId();
		Customer custObj = new DbCustomer().getCustomerById(customer_id+"");
		order.setCustomer(custObj);
		System.out.println("Customer is seleceted");
	}
}
